package mobi.weq.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import mobi.weq.db.Database;
import mobi.weq.model.UserFeedback;
import mobi.weq.util.Util;
import mobi.weq.util.edinburgh.EdinburghMap;

public class UserFeedbackDao {
	private String tableNamePrefix = "uo";

	public String getNDaysBackTable(int nDays) {
		Date d = new Date();
		return Util.getNDaysBackTableFromDate(tableNamePrefix, nDays, d);
	}

	public synchronized int addFeedback(UserFeedback uf, Date d) throws SQLException {
		return addFeedbackInTable(uf, Util.getTableName(tableNamePrefix, d));
	}

	public synchronized int[] addFeedbacks(ArrayList<UserFeedback> records, Date d) throws SQLException {
		return addFeedbacksInTable(records, Util.getTableName(tableNamePrefix, d));
	}

	public synchronized int addFeedback(UserFeedback uf) throws SQLException {
		return addFeedbackInTable(uf, Util.getTableName(tableNamePrefix, new Date()));
	}

	public synchronized int[] addFeedbacks(ArrayList<UserFeedback> records) throws SQLException {
		return addFeedbacksInTable(records, Util.getTableName(tableNamePrefix, new Date()));
	}

	public synchronized int addFeedbackInTable(UserFeedback ob, String tableName) throws SQLException {
		Database.createTableIfNotExist(tableName, tableNamePrefix + "_template");
		Connection c = Database.getConnection();
		String query = "insert into " + tableName + "(id,toleranceLevel,latitude,longitude,timestamp) values(?,?,?,?,?)";
		PreparedStatement ps = c.prepareStatement(query);
		ps.setString(1, ob.getId());
		ps.setInt(2, ob.getToleranceLevel());
		ps.setDouble(3, ob.getLatitude());
		ps.setDouble(4, ob.getLongitude());
		Date d = ob.getTimestamp();
		if (d != null)
			ps.setTimestamp(5, new java.sql.Timestamp(d.getTime()));
		else
			ps.setDate(5, null);

		return ps.executeUpdate();
	}

	public synchronized int[] addFeedbacksInTable(List<UserFeedback> feedbacks, String tableName) throws SQLException {
		Database.createTableIfNotExist(tableName, tableNamePrefix + "_template");

		Connection c = Database.getConnection();
		String query = "insert into " + tableName + "(id,toleranceLevel,latitude,longitude,timestamp) values(?,?,?,?,?)";
		PreparedStatement ps = c.prepareStatement(query);
		c.setAutoCommit(false);

		for (UserFeedback userFeedback : feedbacks) {
			ps.setString(1, userFeedback.getId());
			ps.setInt(2, userFeedback.getToleranceLevel());
			ps.setDouble(3, userFeedback.getLatitude());
			ps.setDouble(4, userFeedback.getLongitude());
			Date d = userFeedback.getTimestamp();
			if (d != null)
				ps.setTimestamp(5, new java.sql.Timestamp(d.getTime()));
			else
				ps.setDate(5, null);
			ps.addBatch();
		}
		int[] counts = ps.executeBatch();
		c.commit();

		System.out.println(counts.length + " :Rows Added");
		return counts;
	}

	private List<UserFeedback> getFeedbackFromResultSet(ResultSet rs) throws SQLException {
		List<UserFeedback> feedbacks = new ArrayList<UserFeedback>();

		while (rs.next()) {
			UserFeedback uf = new UserFeedback();
			uf.setId(rs.getString(1));
			uf.setToleranceLevel(rs.getInt(2));
			uf.setLatitude(rs.getDouble(3));
			uf.setLongitude(rs.getDouble(4));
			uf.setTimestamp(rs.getTimestamp(5));
			feedbacks.add(uf);
		}
		return feedbacks;
	}

	public List<UserFeedback> getAllFeedbacks() throws SQLException {
		Connection c = Database.getConnection();
		String query = "select * from UserFeedback";
		PreparedStatement ps = c.prepareStatement(query);

		ResultSet rs = ps.executeQuery();
		return getFeedbackFromResultSet(rs);
	}

	public List<UserFeedback> getLatestFeedbackForThePosition(double lat, double lng) throws SQLException {
		Connection c = Database.getConnection();
		String query = "select * from " + tableNamePrefix
				+ " where ? >= latitude AND ? <=latitude AND  ? >= longitude AND ? <=longitude order by timestamp desc limit 1";
		PreparedStatement ps = c.prepareStatement(query);
		ps.setDouble(1, lat);
		ps.setDouble(2, lat - EdinburghMap.GRID_SIZE);
		ps.setDouble(3, lng);
		ps.setDouble(4, lng - EdinburghMap.GRID_SIZE);
		ResultSet rs = ps.executeQuery();

		return getFeedbackFromResultSet(rs);
	}

	public List<UserFeedback> getGridsWithLatestFeedbackInTheBounds(double latitude1, double longitude1, double latitude2, double longitude2, int hour)
			throws SQLException {
		Connection c = Database.getConnection();
		String table = getNDaysBackTable(0);
		System.out.println(table);
		String query = "select * from " + table + " where latitude >= ? and latitude <= ? and longitude >= ? and longitude <= ? and HOUR(timestamp)=?";

		System.out.println(query);
		PreparedStatement ps = c.prepareStatement(query);
		ps.setDouble(1, latitude1 - EdinburghMap.GRID_SIZE);
		ps.setDouble(2, latitude2 + EdinburghMap.GRID_SIZE);
		ps.setDouble(3, longitude1 - EdinburghMap.GRID_SIZE);
		ps.setDouble(4, longitude2 + EdinburghMap.GRID_SIZE);
		ps.setInt(5, hour);

		System.out.println(ps.toString());
		ResultSet rs = ps.executeQuery();

		return getFeedbackFromResultSet(rs);
	}
}
